﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.DAL;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL; 
using KPIS.DBM;

using KPIS.GERP.GHRMS.DALFactory;

namespace KPIS.GERP.GHRMS.BLL
{
    public class RC_BackSamePositionBLL


    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);




        public void insert(int cmd_seq, int emp_seq, string start_date, int new_pos_seq, 
            string new_pos_ref, string new_pos_name, int new_class_seq, int new_job_site_seq, 
            string new_job_site_name, double new_salary, int edu_his_seq, string old_pos_name, 
            string old_pos_ref, int old_pos_type_seq, int old_class_seq, string old_job_site_name,
            double old_salary, int move_seq, string remarks, int created_by, int new_pos_type_seq)

        {
            try
                          
            {
                DateTime startDate;

                startDate  = Convert.ToDateTime(start_date);  

           //     startDate = Convert.ToDateTime(Utils.Date.ToEngFormatDate(start_date));     
                
                dbManager.Open();
                dbManager.CreateParameters(23);
                dbManager.AddParameters(0, "@cmd_seq", cmd_seq);
                dbManager.AddParameters(1, "@emp_seq", emp_seq);
                dbManager.AddParameters(2, "@start_date", startDate);
                dbManager.AddParameters(3, "@new_pos_seq", new_pos_seq);
                dbManager.AddParameters(4, "@new_pos_ref", new_pos_ref);
                dbManager.AddParameters(5, "@new_pos_name", new_pos_name);
                dbManager.AddParameters(6, "@new_class_seq", new_class_seq);
                dbManager.AddParameters(7, "@new_job_site_seq", new_job_site_seq);
                dbManager.AddParameters(8, "@new_job_site_name", new_job_site_name);
                dbManager.AddParameters(9, "@new_salary", new_salary);
                dbManager.AddParameters(10, "@edu_his_seq", edu_his_seq);
                dbManager.AddParameters(11, "@old_pos_name", old_pos_name);
                dbManager.AddParameters(12, "@move_seq", move_seq);
                dbManager.AddParameters(13, "@remarks", remarks);
                dbManager.AddParameters(14, "@record_status", SystemInfo._ActiveRecordStatus  );
                dbManager.AddParameters(15, "@created_by", created_by);
                dbManager.AddParameters(16, "@created_when", DateTime.Today);
                dbManager.AddParameters(17, "@old_pos_type_seq", old_pos_type_seq );
                dbManager.AddParameters(18, "@new_pos_type_seq", new_pos_type_seq);
                dbManager.AddParameters(19, "@old_salary", old_salary );
                dbManager.AddParameters(20, "@old_class_seq", old_class_seq);
                dbManager.AddParameters(21, "@old_pos_ref", old_pos_ref);
                dbManager.AddParameters(22, "@old_job_site_name", old_job_site_name);  
                  string sql;

                  sql = " insert into hrm_ps_command_dtls"
                + "(cmd_seq,emp_seq,start_date,new_pos_seq,new_pos_ref,new_pos_name,"
                + "new_class_seq,new_job_site_seq,new_job_site_name,new_salary,edu_his_seq,"
                + "old_pos_name,move_seq,remarks,record_status,created_by,created_when,new_pos_type_seq,"
                + "old_salary,old_pos_type_seq,old_class_seq,old_pos_ref,old_job_site_name)"
                
                + " values(@cmd_seq,@emp_seq,@start_date,@new_pos_seq,@new_pos_ref,@new_pos_name,@new_class_seq,"
                + "@new_job_site_seq,@new_job_site_name,@new_salary,@edu_his_seq,@old_pos_name,@move_seq,"
                + " @remarks,@record_status,@created_by,@created_when,@new_pos_type_seq,"
                + " @old_salary,@old_pos_type_seq,@old_class_seq,@old_pos_ref,@old_job_site_name)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);     

            }

            catch(Exception ex)
             {

                 throw new Exception(ex.Message);  
            }

          finally 
                 {
                     dbManager.Dispose(); 

            }

        }





        public void Update(int cmd_dtls_seq,string start_date, int new_pos_seq,
            string new_pos_ref, string new_pos_name, int new_class_seq, int new_job_site_seq,
            string new_job_site_name, double new_salary, int edu_his_seq, string old_pos_name,
            string old_pos_ref, int old_pos_type_seq, int old_class_seq, string old_job_site_name,
            double old_salary, int move_seq, string remarks, int updated_by, int new_pos_type_seq)
        {
            try
            {
                DateTime startDate;

                startDate = Convert.ToDateTime(start_date);

                //     startDate = Convert.ToDateTime(Utils.Date.ToEngFormatDate(start_date));     

                dbManager.Open();
                dbManager.CreateParameters(22);
                dbManager.AddParameters(0, "@cmd_dtls_seq", cmd_dtls_seq);
                dbManager.AddParameters(1, "@start_date", startDate);
                dbManager.AddParameters(2, "@new_pos_seq", new_pos_seq);
                dbManager.AddParameters(3, "@new_pos_ref", new_pos_ref);
                dbManager.AddParameters(4, "@new_pos_name", new_pos_name);
                dbManager.AddParameters(5, "@new_class_seq", new_class_seq);
                dbManager.AddParameters(6, "@new_job_site_seq", new_job_site_seq);
                dbManager.AddParameters(7, "@new_job_site_name", new_job_site_name);
                dbManager.AddParameters(8, "@new_salary", new_salary);
                dbManager.AddParameters(9, "@edu_his_seq", edu_his_seq);
                dbManager.AddParameters(10, "@old_pos_name", old_pos_name);
                dbManager.AddParameters(11, "@move_seq", move_seq);
                dbManager.AddParameters(12, "@remarks", remarks);
                dbManager.AddParameters(13, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(14, "@updated_by", updated_by);
                dbManager.AddParameters(15, "@updated_when", DateTime.Today);
                dbManager.AddParameters(16, "@old_pos_type_seq", old_pos_type_seq);
                dbManager.AddParameters(17, "@new_pos_type_seq", new_pos_type_seq);
                dbManager.AddParameters(18, "@old_salary", old_salary);
                dbManager.AddParameters(19, "@old_class_seq", old_class_seq);
                dbManager.AddParameters(20, "@old_pos_ref", old_pos_ref);
                dbManager.AddParameters(21, "@old_job_site_name", old_job_site_name);
                string sql;

                sql = " update hrm_ps_command_dtls"
                + " set start_date=@start_date,"
                + " new_pos_seq=@new_pos_seq,"
                + " new_pos_ref=@new_pos_ref,"
                + " new_pos_name=@new_pos_name,"
                + " new_class_seq=@new_class_seq,"
                + " new_job_site_seq=@new_job_site_seq,"
                + " new_job_site_name=@new_job_site_name,"
                + " new_salary=@new_salary,"
                + " edu_his_seq=@edu_his_seq,"
                + " old_pos_name=@old_pos_name,"
                + " move_seq=@move_seq,"
                + " remarks=@remarks,"
                + " record_status=@record_status,"
                + " updated_by=@updated_by,"
                + " updated_when=@updated_when,"
                + " new_pos_type_seq=@new_pos_type_seq,"
                + " old_salary=@old_salary,"
                + " old_pos_type_seq=@old_pos_type_seq,"
                + " old_class_seq=@old_class_seq,"
                + " old_pos_ref=@old_pos_ref,"
                + " old_job_site_name=@old_job_site_name"
                + " where cmd_dtls_seq=@cmd_dtls_seq";


                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }

            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }

            finally
            {
                dbManager.Dispose();

            }

        }






        public DataTable Select(int cmd_seq)
        {
            try
            {
                dbManager.Open();
                string sql;
                 sql = "SELECT"
                    + " concat(prefix_name,'  ',first_name,'    ',last_name) AS full_name,"
                    + " cmd_dtls.cmd_dtls_seq,"
                    + " cmd_dtls.*,"
                    + " cmd_dtls.emp_seq,"
                    + " cmd_dtls.remarks,"
                    + " cmd_dtls.start_date,"
                    + " cmd_dtls.old_salary,"
                    + " cmd_dtls.old_pos_ref,"
                    + " cmd_dtls.old_pos_name,"
                    + " cmd_dtls.old_class_seq,"
                    + " cmd_dtls.old_job_site_seq,"
                    + " cmd_dtls.old_job_site_name,"
                    + " cmd_dtls.new_pos_seq,"
                    + " cmd_dtls.new_pos_ref,"
                    + " cmd_dtls.new_pos_name,"
                    + " cmd_dtls.new_class_seq,"
                    + " cmd_dtls.new_job_site_seq,"
                    + " cmd_dtls.new_job_site_name,"
                    + " cmd_dtls.move_seq,"
                    + " cmd_dtls.new_salary,"
                    + " mas_name_prefixes.prefix_name,"
                    + " hrm_mp_classes.class_name"
                    + " FROM"
                    + " hrm_ps_command_dtls AS cmd_dtls"
                    + " Inner Join hrm_ps_employees ON hrm_ps_employees.emp_seq = cmd_dtls.emp_seq"
                    + " Inner Join mas_name_prefixes ON mas_name_prefixes.name_prefix_seq = hrm_ps_employees.name_prefix_seq"
                    + " Inner Join hrm_mp_classes ON hrm_mp_classes.class_seq = cmd_dtls.new_class_seq"
                    + " where cmd_dtls.record_status = '" + SystemInfo._ActiveRecordStatus + "'"
                    + " and cmd_dtls.cmd_seq=" + cmd_seq ;

//                sql = "SELECT"
//+ " concat(prefix_name,'  ',first_name,'    ',last_name) as full_name,"
//+ " cmd_add.job_site_name,"
//+ " cmd_add.salary,"
//+ " cmd_add.pos_name,"
//+ " class.class_name,"
//+ " cmd_add.remarks,"
//+ " prefix.prefix_name,"
//+ " cmd_add.cmd_add_seq,"
//+ " cmd_add.tmp_edu_seq,"
//+ " cmd_add.move_seq,"
//+ " cmd_add.old_salary,"
//+ " cmd_add.old_job_site_name,"
//+ " cmd_add.old_pos_type_seq,"
//+ " cmd_add.pos_ref_no,"
//+ " cmd_add.pos_seq,"
//+ " cmd_add.class_seq,"
//+ " cmd_add.old_pos_name,"
//+ " cmd_add.old_class_seq,"
//+ " cmd_add.start_date,"
//+ " cmd_add.pos_type_seq,"
//+ " cmd_add.job_site_seq,"
//+ " cmd_add.tmp_emp_seq"
//+ " FROM"
//+ " hrm_tmp_employees AS tmp_emp"
//+ " Inner Join hrm_ps_command_add AS cmd_add ON cmd_add.tmp_emp_seq = tmp_emp.tmp_emp_seq"
//+ " Inner Join hrm_mp_classes AS class ON cmd_add.class_seq = class.class_seq"
//+ " Inner Join mas_name_prefixes AS prefix ON tmp_emp.name_prefix_seq = prefix.name_prefix_seq"
//+ " where cmd_add.record_status = '" + SystemInfo._ActiveRecordStatus + "'";
                   

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }


        public Boolean chkDup(int cmd_seq,int emp_seq)
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = "select * from hrm_ps_command_dtls"
                    + " where cmd_seq = " + cmd_seq
                    + " and emp_seq = " + emp_seq
                + " and record_status='" + SystemInfo._ActiveRecordStatus + "'"; 
                DataSet ds = new DataSet(); 
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                if (dt.Rows.Count > 1)
                {
                    return true;
                }
                else
                {
                    return false;
                }


               
            }

            catch (Exception ex)
            
            {
                throw(new Exception(ex.Message)) ;  
            }

            finally
            {
                dbManager.Dispose();   
            }


        }


        public DataTable SelectEmp()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT "
+ " concat(prefix.prefix_name,' ',emp.first_name,'   ',emp.last_name) as full_name, "
+ " emp.emp_seq "
+ " FROM"
+ " hrm_ps_employees AS emp"
+ " Inner Join mas_name_prefixes AS prefix ON emp.name_prefix_seq = prefix.name_prefix_seq"
+ " where emp.record_status = '" + SystemInfo._ActiveRecordStatus  + "'";





                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }

        public DataTable SelectEmpByID(int id)
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT "
+ " concat(prefix.prefix_name,' ',emp.first_name,'   ',emp.last_name) as full_name, "
+ " emp.emp_seq "
+ " FROM"
+ " hrm_ps_employees AS emp"
+ " Inner Join mas_name_prefixes AS prefix ON emp.name_prefix_seq = prefix.name_prefix_seq"
+ " where emp.record_status = '" + SystemInfo._ActiveRecordStatus + "'"
+ " and emp_seq=" + id;





                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }

        public DataTable SelectStatus()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " select * from mas_record_statuses  "
                    + " where record_status in('" + SystemInfo._ActiveRecordStatus  + "','" + SystemInfo._CancelRecordStatus  + "')";
             
                DataSet ds = new DataSet();
                DataTable dt;
                                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();  
            }

        }


 
 
        public void Delete(int cmd_dtls_seq,int UpdatedBy)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@cmd_dtls_seq", cmd_dtls_seq);
                dbManager.AddParameters(2, "@updated_by", UpdatedBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                string sql;
               

                sql = " update hrm_ps_command_dtls set "
              + " record_status=@record_status"
              + ", updated_by = @updated_by"
              + ", updated_when = @updated_when"
              + " where cmd_dtls_seq = @cmd_dtls_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }
            catch (Exception ex)
            {
                throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }









        public DataTable SelectDialogOldPos()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " select field_pos_seq,field_pos_line"
                    + " from hrm_mp_field_positions"
+ " where record_status = '" + SystemInfo._ActiveRecordStatus + "'";






                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }



        }

        public DataTable SelectDialogEntPos()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " select field_pos_seq,field_pos_line"
                    + " from hrm_mp_field_positions"
+ " where record_status = '" + SystemInfo._ActiveRecordStatus + "'";






                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }



        }

        public DataTable SelectDialogPos()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " select *"
                    + " from hrm_mp_positions"
+ " where record_status = '" + SystemInfo._ActiveRecordStatus + "'";






                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }



        }


        public DataTable SelectComboEdu(int emp_seq)
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " select "
+ " qua.edu_qual_name,"
+ " qua.edu_qual_seq,"
+ " emp_edu.emp_seq"
+ " FROM"
+ " mas_edu_qualifications AS qua"
+ " Inner Join hrm_ps_emp_educations AS emp_edu ON qua.edu_qual_seq = emp_edu.edu_qual_seq"
+ " where emp_edu.record_status = '" + SystemInfo._ActiveRecordStatus + "'"
+ " and emp_edu.emp_seq=" + emp_seq;

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }


            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }



        }



        public DataTable BindTxtMajor(int edu_qual_seq)
        {

            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT"
+ " emp_edu.edu_qual_seq,"
+ " emp_edu.end_date,"
+ " major.edu_major_name,"
+ " ins.edu_ins_name"
+ " FROM"
+ " mas_edu_majors AS major"
+ " Inner Join hrm_ps_emp_educations AS emp_edu ON major.edu_major_seq = emp_edu.edu_major_seq"
+ " Inner Join mas_edu_institutes AS ins ON emp_edu.edu_ins_seq = ins.edu_ins_seq"

+ " where emp_edu.record_status = '" + SystemInfo._ActiveRecordStatus + "'"
+ " and emp_edu.edu_qual_seq = " + edu_qual_seq;

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }


            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }


        }




        public DataTable BindPosition(int pos_seq)
        {

            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT"
+ " pos.pos_seq,"
+ " pos.pos_ref,"
+ " pos.pos_name,"
+ " pos.job_site_seq,"
+ " field_pos.pos_type_seq"
+ " FROM"
+ " hrm_mp_positions AS pos"
+ " Inner Join hrm_mp_field_positions AS field_pos ON pos.field_pos_seq = field_pos.field_pos_seq"

+ " where pos.record_status = '" + SystemInfo._ActiveRecordStatus + "'"
+ " and pos.pos_seq = " + pos_seq;

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }


            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }


        }


        public DataTable BindPositionType(int field_pos_seq)
        {

            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT"
+ " field_pos.field_pos_seq,"
+ " field_pos.field_pos_line,"
+ " type_pos.pos_type_name,"
+ " type_pos.pos_type_seq"
+ " FROM"
+ " hrm_mp_field_positions AS field_pos"
+ " Inner Join hrm_mp_position_types AS type_pos ON field_pos.pos_type_seq = type_pos.pos_type_seq"

+ " where field_pos.record_status = '" + SystemInfo._ActiveRecordStatus + "'"
+ " and field_pos.field_pos_seq = " + field_pos_seq;

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }


            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }


        }





    







     

   
    }
}
